from backend.app.app.db.session_public import session_pub
import backend.app.app.models.db_owi_public_models as md_pub
from backend.app.app.db.session_private import session_pri
import backend.app.app.models.db_owi_private_models as md_pri
from backend.app.app.crud_pro.tb_userpro_input import create_tree
from backend.app.app.models.well import data_save


#    读取user_id
def get_u_id():
    try:
        result = session_pri.query(md_pri.TbYearProfitLossAn.user_id).all()
        return result
    except:
        print('查找失败')
        session_pri.rollback()
        return False


#    读取pro_id
def get_p_id():
    try:
        result = session_pri.query(md_pri.TbYearProfitLossAn.project_id).all()
        return result
    except:
        print('查找失败')
        session_pri.rollback()
        return False


#   单井盈亏平衡分析 -> 读取页面数据
def query_data(query_key):
    try:
        project_inf = session_pri.query(
            md_pri.TbYearProfitLossAn.user_id,
            md_pri.TbYearProfitLossAn.project_id,
            md_pri.TbYearProfitLossAn.dvpUnit_Name,
            md_pri.TbYearProfitLossAn.stimType_Name,
            md_pri.TbYearProfitLossAn.year,
            md_pri.TbYearProfitLossAn.well_Name,
            md_pri.TbYearProfitLossAn.calcu_type,
            md_pri.TbYearProfitLossAn.analysis_data,
            md_pri.TbYearProfitLossAn.parm_set_data,
            md_pri.TbYearProfitLossAn.OilPriceCritical,
            md_pri.TbYearProfitLossAn.IncreOilCritical,
            md_pri.TbYearProfitLossAn.StimCostCritical,
            md_pri.TbYearProfitLossAn.Stim_ValidPeriod_Average,
            md_pri.TbYearProfitLossAn.Stim_IncreOil_Average,
            md_pri.TbYearProfitLossAn.StimOil_CommodityRatio,
            md_pri.TbYearProfitLossAn.Oil_Price,
            md_pri.TbYearProfitLossAn.OilSale_TotalTaxRatio,
            md_pri.TbYearProfitLossAn.Money_Discount,
            md_pri.TbYearProfitLossAn.Oil_OprCost,
            md_pri.TbYearProfitLossAn.StimCost_Average
        ).filter(
            md_pri.TbYearProfitLossAn.user_id == query_key.user_id,
            md_pri.TbYearProfitLossAn.project_id == query_key.project_id
        ).all()
        # print(project_inf)
        return project_inf
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        print(e.__traceback__.tb_frame.f_globals["__file__"])
        print('查找失败')
        return False


#   单井盈亏平衡分析 -> 保存页面数据[保存数据]
def save_project_db(s_data):
    try:
        session_pri.add(
            md_pri.TbYearProfitLossAn(
                user_id=s_data.User_ID,
                project_id=s_data.Proj_ID,
                dvpUnit_Name=s_data.dvpUnit_Name,
                stimType_Name=s_data.stimType_Name,
                year=s_data.year,
                well_Name=s_data.well_Name,
                calcu_type=s_data.calculation_type,
                analysis_data=s_data.analysis_data,
                parm_set_data=s_data.parameter_set,
                OilPriceCritical=s_data.OilPriceCritical,
                IncreOilCritical=s_data.IncreOilCritical,
                StimCostCritical=s_data.StimCostCritical,
                Stim_ValidPeriod_Average=s_data.Stim_ValidPeriod_Average,
                Stim_IncreOil_Average=s_data.Stim_IncreOil_Average,
                StimOil_CommodityRatio=s_data.StimOil_CommodityRatio,
                Oil_Price=s_data.Oil_Price,
                OilSale_TotalTaxRatio=s_data.OilSale_TotalTaxRatio,
                Money_Discount=s_data.Money_Discount,
                Oil_OprCost=s_data.Oil_OprCost,
                StimCost_Average=s_data.StimCost_Average
            ))
        session_pri.commit()
        print('储存成功')
        return True
    except Exception as e:
        print(e)
        print('储存失败')
        session_pri.rollback()
        return False


#   单井盈亏平衡分析 -> 保存页面数据[更新数据]
def updata_project_db(s_data):
    try:
        session_pri.query(md_pri.TbYearProfitLossAn).filter(
            md_pri.TbYearProfitLossAn.user_id == s_data.User_ID,
            md_pri.TbYearProfitLossAn.project_id == s_data.Proj_ID
        ).update(
            {
                md_pri.TbYearProfitLossAn.dvpUnit_Name: s_data.dvpUnit_Name,
                md_pri.TbYearProfitLossAn.stimType_Name: s_data.stimType_Name,
                md_pri.TbYearProfitLossAn.year: s_data.year,
                md_pri.TbYearProfitLossAn.well_Name: s_data.well_Name,
                md_pri.TbYearProfitLossAn.calcu_type: s_data.calculation_type,
                md_pri.TbYearProfitLossAn.analysis_data: s_data.analysis_data,
                md_pri.TbYearProfitLossAn.parm_set_data: s_data.parameter_set,
                md_pri.TbYearProfitLossAn.OilPriceCritical: s_data.OilPriceCritical,
                md_pri.TbYearProfitLossAn.IncreOilCritical: s_data.IncreOilCritical,
                md_pri.TbYearProfitLossAn.StimCostCritical: s_data.StimCostCritical,
                md_pri.TbYearProfitLossAn.Stim_ValidPeriod_Average: s_data.Stim_ValidPeriod_Average,
                md_pri.TbYearProfitLossAn.Stim_IncreOil_Average: s_data.Stim_IncreOil_Average,
                md_pri.TbYearProfitLossAn.StimOil_CommodityRatio: s_data.StimOil_CommodityRatio,
                md_pri.TbYearProfitLossAn.Oil_Price: s_data.Oil_Price,
                md_pri.TbYearProfitLossAn.OilSale_TotalTaxRatio: s_data.OilSale_TotalTaxRatio,
                md_pri.TbYearProfitLossAn.Money_Discount: s_data.Money_Discount,
                md_pri.TbYearProfitLossAn.Oil_OprCost: s_data.Oil_OprCost,
                md_pri.TbYearProfitLossAn.StimCost_Average: s_data.StimCost_Average
            }
        )
        session_pri.commit()
        return True
    except Exception as e:
        print(e)
        session_pri.rollback()
        print("更新数据失败")
        return False


# 单井盈亏平衡分析 -> 读库
def read_lib(well_name, stim_type, years):
    if not well_name and stim_type and years:
        print("数据有误")
        return False
    return_data = dict()
    well_name = well_name[0]
    stim_type = stim_type[0]
    # 判断井号是否存在
    is_well_existed = session_pub.query(
        md_pub.TbWellbasic
    ).filter(
        md_pub.TbWellbasic.Well_Name == well_name
    ).count()
    if not is_well_existed:
        print("井号不存在")
        return False
    # print(well_name, stim_type,years)
    year, month, day = years.split('-')
    # 获取有效期和措施增油量
    try:
        data_one = session_pub.query(
            md_pub.TbOilwellstimResult.Stim_ValidPeriod,
            md_pub.TbOilwellstimResult.Stim_IncreOil
        ).filter(
            md_pub.TbOilwellstimResult.Well_Name == well_name,
            md_pub.TbOilwellstimResult.StimType_Name == stim_type,
            md_pub.TbOilwellstimResult.Stim_Date == years
        ).first()

        return_data.update(
            dict(zip(data_one.keys(), data_one) if data_one else {'Stim_ValidPeriod': None, 'Stim_IncreOil': None}))
        print(return_data)
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取该井号的隶属开发单元
    dvp_unit_name = session_pub.query(
        md_pub.TbWellbasic.Belong_DvpUnit_Name
    ).filter(
        md_pub.TbWellbasic.Well_Name == well_name
    ).first()[0]

    # 获取作业费用
    try:
        stim_cost = session_pub.query(
            md_pub.TbYearDvpunitOilstimCost.Stim_Cost
        ).filter(
            md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name == dvp_unit_name,
            md_pub.TbYearDvpunitOilstimCost.Year == year,
            md_pub.TbYearDvpunitOilstimCost.StimType_Name == stim_type
        ).first()
        return_data.update({'Stim_Cost': stim_cost[0] if stim_cost else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取原油商品率，原油价格
    try:
        data_two = session_pub.query(
            md_pub.TbYearDvpunitOilprice.Oil_CommodityRatio,
            md_pub.TbYearDvpunitOilprice.Oil_Price
        ).filter(
            md_pub.TbYearDvpunitOilprice.DvpUnit_Name == dvp_unit_name,
            md_pub.TbYearDvpunitOilprice.Year == year
        ).first()
        return_data.update(
            dict(zip(data_two.keys(), data_two)) if data_two else {'Oil_CommodityRatio': None, 'Oil_Price': None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取操作吨油成本
    try:
        oil_opr_cost = session_pub.query(
            md_pub.TbYearDvpunitOilprodcost.Oil_OprCost
        ).filter(
            md_pub.TbYearDvpunitOilprodcost.Year == year,
            md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == dvp_unit_name
        ).first()
        return_data.update({'Oil_OprCost': oil_opr_cost[0] if oil_opr_cost else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 销售税费率
    try:
        oil_sale_total_tax_ratio = session_pub.query(
            md_pub.TbYearDvpunitOilsalecostRatio.OilSale_TotalTaxRatio
        ).filter(
            md_pub.TbYearDvpunitOilsalecostRatio.Year == year,
            md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == dvp_unit_name
        ).first()
        return_data.update({'OilSale_TotalTaxRatio': oil_sale_total_tax_ratio[0] if oil_sale_total_tax_ratio else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取资金贴现率
    try:
        money_discount = session_pub.query(
            md_pub.TbYearDvpunitDiscount.Money_Discount
        ).filter(
            md_pub.TbYearDvpunitDiscount.DvpUnit_Name == dvp_unit_name,
            md_pub.TbYearDvpunitDiscount.Year == year
        ).first()
        return_data.update({'Money_Discount': money_discount[0] if money_discount else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    return return_data

    pass


# 获取单井、措施类别、措施日期
def get_well_stim_date(dvp_unit_name):
    return_data = dict()
    # 获取该开发单元下所有井号
    try:
        well_names = session_pub.query(
            md_pub.TbWellbasic.Well_Name
        ).filter(
            md_pub.TbWellbasic.Belong_DvpUnit_Name == dvp_unit_name
        ).all()
        return_data.update({'Well_Name': [well[0] for well in well_names] if well_names else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取措施类别
    try:
        stim_type = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).all()
        stim_type = [dict(zip(type.keys(), type)) for type in stim_type]
        # 处理措施数据，生成树形结构
        stim_type_tree = list()
        for type in stim_type:
            temp_dic = dict()
            if type['StimType_Rank'] == '1':
                temp_dic['id'] = type['StimType_ID']
                temp_dic['title'] = type['StimType_Name']
                children = create_tree(stim_type, type['StimType_Name'], "措施")
                if children:
                    temp_dic['children'] = children
                stim_type_tree.append(temp_dic)
        return_data.update({'StimType_Name': stim_type_tree if stim_type_tree else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    # 获取措施日期
    try:
        stim_date = session_pub.query(
            md_pub.TbOilwellstimResult.Stim_Date
        ).distinct().all()
        return_data.update({'Stim_Date': [date[0] for date in stim_date] if stim_date else None})
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)

    return return_data
